--drop table if exists dongfeng.navinfo_stage_a204;
--创建外部表
create external table if not exists dongfeng.navinfo_stage_a204 (
      latitude                       int      comment    '纬度',
      longitude                      int      comment    '经度',
      originallat                    int      comment    '原始纬度',
      originallng                    int      comment    '原始经度',
      height                         int      comment    '海拔',
      direction                      int      comment    '方向',
      gpsdate                        bigint   comment    '时间',
      totaldrivemileage              bigint   comment    '总行驶里程',
      gpsspeed                       int      comment    'GPS速度',
      faultcnt                       int      comment    '当前故障个数',
      faultlist                      string   comment    '故障列表',
      sourceaddress                  int      comment    '源地址',
      collectdate                    bigint   comment    '采集时间',
      datalength                     int      comment    '后续数据长度',
      atmosphericpressure            double   comment    '大气压力',
      atmospherictemperature         double   comment    '环境温度',
      fakethrottleopening            double   comment    'VECU伪油门开度',
      t50status                      int      comment    'T50状态',
      throttleopening                double   comment    '加速踏板位置',
      dashboardspeed                 double   comment    '仪表车速',
      totalmileage                   double   comment    '累计里程',
      subtotalmileage                double   comment    '小计里程',
      intakeairtemperature           double   comment    'SCR进口温度',
      exhaustgastemperature          double   comment    'SCR出口温度',
      noxincontr                     double   comment    '进口NOx浓度',
      dpfpressurediff                double   comment    'DPF压差',
      noxoutcontr                    double   comment    '出口NOx浓度',
      urealevel                      double   comment    '尿素液位',
      ureatemp                       double   comment    '尿素罐内尿素温度',
      refertorque                    int      comment    '发动机参考扭矩',
      egrstatus                      int      comment    'EGR阀状态',
      actualoutputtorque             double   comment    '发动机实际扭矩百分比',
      enginespeed                    double   comment    '发动机转速',
      outflow                        double   comment    '排气流量',
      innox                          int      comment    '进口NOx露点释放',
      outnox                         int      comment    '出口NOx露点释放',
      inflow                         double   comment    '发动机进气流量',
      watertemperature               int      comment    '发动机冷却液温度',
      currentgear                    int      comment    'AMT变速箱当前档位',
      intakemanifoldpressure         int      comment    '发动机进气歧管压力',
      intakemanifoldairtemperature   int      comment    '发动机进气歧管温度',
      outtakeairtemperature          double   comment    '排气温度',
      realtimeoilconsumption         double   comment    '发动机瞬时油耗(km/L)',
      averagefuelconsumption         double   comment    '发动机平均油耗(km/L)',
      throttle1valve                 double   comment    '节气门1位置',
      eecugear                       int      comment    'EECU计算变速箱档位',
      oilpressure                    int      comment    '发动机机油压力',
      terminalid                     bigint   comment    '终端号',
      part_time                           INT           COMMENT '分区时间'
) row format delimited fields terminated by '\001' STORED AS TEXTFILE location '/user/data/dongfeng/stage_import_a204';

--创建目标表
create table if not exists dongfeng.navinfo_parquet_a204(
   latitude                       int      comment    '纬度',
   longitude                      int      comment    '经度',
   originallat                    int      comment    '原始纬度',
   originallng                    int      comment    '原始经度',
   height                         int      comment    '海拔',
   direction                      int      comment    '方向',
   gpsdate                        bigint   comment    '时间',
   totaldrivemileage              bigint   comment    '总行驶里程',
   gpsspeed                       int      comment    'GPS速度',
   faultcnt                       int      comment    '当前故障个数',
   faultlist                      string   comment    '故障列表',
   sourceaddress                  int      comment    '源地址',
   collectdate                    bigint   comment    '采集时间',
   datalength                     int      comment    '后续数据长度',
   atmosphericpressure            double   comment    '大气压力',
   atmospherictemperature         double   comment    '环境温度',
   fakethrottleopening            double   comment    'VECU伪油门开度',
   t50status                      int      comment    'T50状态',
   throttleopening                double   comment    '加速踏板位置',
   dashboardspeed                 double   comment    '仪表车速',
   totalmileage                   double   comment    '累计里程',
   subtotalmileage                double   comment    '小计里程',
   intakeairtemperature           double   comment    'SCR进口温度',
   exhaustgastemperature          double   comment    'SCR出口温度',
   noxincontr                     double   comment    '进口NOx浓度',
   dpfpressurediff                double   comment    'DPF压差',
   noxoutcontr                    double   comment    '出口NOx浓度',
   urealevel                      double   comment    '尿素液位',
   ureatemp                       double   comment    '尿素罐内尿素温度',
   refertorque                    int      comment    '发动机参考扭矩',
   egrstatus                      int      comment    'EGR阀状态',
   actualoutputtorque             double   comment    '发动机实际扭矩百分比',
   enginespeed                    double   comment    '发动机转速',
   outflow                        double   comment    '排气流量',
   innox                          int      comment    '进口NOx露点释放',
   outnox                         int      comment    '出口NOx露点释放',
   inflow                         double   comment    '发动机进气流量',
   watertemperature               int      comment    '发动机冷却液温度',
   currentgear                    int      comment    'AMT变速箱当前档位',
   intakemanifoldpressure         int      comment    '发动机进气歧管压力',
   intakemanifoldairtemperature   int      comment    '发动机进气歧管温度',
   outtakeairtemperature          double   comment    '排气温度',
   realtimeoilconsumption         double   comment    '发动机瞬时油耗(km/L)',
   averagefuelconsumption         double   comment    '发动机平均油耗(km/L)',
   throttle1valve                 double   comment    '节气门1位置',
   eecugear                       int      comment    'EECU计算变速箱档位',
   oilpressure                    int      comment    '发动机机油压力',
   terminalid                     bigint   comment    '终端号',
   create_time                        STRING              COMMENT '数据创建时间'
) partitioned by (part_time int) stored as Parquet TBLPROPERTIES('parquet.compression'='SNAPPY');
